package fi.ribanoz.database;

import java.sql.*;
import java.util.ArrayList;

import fi.ribanoz.office.*;
import fi.ribanoz.GUI.*;
import java.util.*;

public class SQL {

	/**
	 * 
	 * This class handles all the database operations.
	 * 
	 * @author Teemu Seppä & Perttu Katainen
	 * 
	 * 
	 */

	private Statement stmt = null;
	private Connection c = null;
	private Scanner read = new Scanner(System.in);
	private ArrayList<String> lines = new ArrayList();
	private String fieldValue;// input value
	private String toString;// output value
	private String fname;
	private String surname;
	private String address;
	private String postcode;
	private String postarea;
	private String phone;
	private String email;

	/**
	 * 
	 * This method creates the SQLite connection to the database called
	 * customer.
	 * 
	 * @return Connection SQLite Connection
	 * @throws Exception
	 *             SQL Connection
	 * 
	 */
	private Connection getSQLConnection() throws Exception {

		Class.forName("org.sqlite.JDBC");
		String db = "jdbc:sqlite:customers.db";
		c = DriverManager.getConnection(db);
		return c;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public void setPostcode(String postcode) {
		this.postcode = postcode;
	}

	public void setPostarea(String postarea) {
		this.postarea = postarea;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}

	public void setFname(String fname) {
		this.fname = fname;
	}

	public void setSurname(String surname) {
		this.surname = surname;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public String getToString() {
		return toString;
	}

	public void setToString(String toString) {
		this.toString = toString;
	}

	public String getFieldValue() {
		return fieldValue;
	}

	public void setFieldValue(String fieldValue) {
		this.fieldValue = fieldValue;

	}

	/**
	 * Creates the customer table to the database.
	 */
	public void createTable() {

		String create_table = "CREATE TABLE IF NOT EXISTS customers"
				+ "(cust_ID INTEGER PRIMARY KEY 		AUTOINCREMENT, "
				+ "first_name	TEXT			NOT NULL, "
				+ "surname 		TEXT			NOT NULL, " + "address 		CHAR(50) 		, "
				+ "postcode 		CHAR(5)			, " + "postarea 		CHAR(25) 		, "
				+ "phone 		CHAR(50) 		, " + "email 		CHAR(50) 		, "
				+ "lastvisit 	DATETIME)";

		try {

			stmt = getSQLConnection().createStatement();
			stmt.execute(create_table);
			stmt.close();
			getSQLConnection().close();

		} catch (Exception e) {
			System.err.println(e.getClass().getName() + "; " + e.getMessage());
			System.exit(0);
		}

		System.out.println("Table created / already found");
	}

	/**
	 * Inserts new customers to the database.
	 */

	public void insertCustomer() {

		/*
		System.out.println("Uuden asiakkaan lisäys.");
		System.out.println("Syötä etunimi: ");
		// fname = read.nextLine();

		System.out.println("Syötä sukunimi: ");
		// surname = read.nextLine();

		System.out.println("Syötä osoite (jos ei tiedossa paina enter): ");
		// address = read.nextLine();

		System.out.println("Syötä postinumero (jos ei tiedossa paina enter): ");
		// postcode = read.nextLine();

		System.out
				.println("Syötä postitoimipaikka (jos ei tiedossa paina enter): ");
		// postarea = read.nextLine();

		System.out
				.println("Syötä puhelinnumero (jos ei tiedossa paina enter): ");
		// phone = read.nextLine();

		System.out.println("Syötä emailosoite (jos ei tiedossa paina enter): ");
		// email = read.nextLine();
*/
		String inject = "INSERT INTO customers(cust_ID,first_name,surname,address,postcode,postarea,phone,email,lastvisit) "
				+ "VALUES (null,'"
				+ fname
				+ "','"
				+ surname
				+ "','"
				+ address
				+ "','"
				+ postcode
				+ "','"
				+ postarea
				+ "','"
				+ phone
				+ "','"
				+ email + "',DATETIME('NOW'));";
		try {

			stmt = getSQLConnection().createStatement();
			stmt.executeUpdate(inject);

			stmt.close();
			getSQLConnection().close();

		} catch (Exception e) {
			System.err.println(e.getClass().getName() + "; " + e.getMessage());
			System.exit(0);
		}
		System.out.println("Customer created successfully");
	}

	/**
	 * Fetches data from database with SELECT query.
	 * 
	 * @throws Exception
	 *             SQLException
	 */

	public ArrayList<String> selectOperation() throws Exception {

		try {
			String value = this.getFieldValue();
			stmt = getSQLConnection().createStatement();
			ResultSet rs = stmt
					.executeQuery("SELECT * FROM customers WHERE first_name="
							+ "'" + value + "'");
			// loop for iterating database
			while (rs.next()) {
				int id = rs.getInt("cust_ID");
				String fname = rs.getString("first_name");
				String lname = rs.getString("surname");
				String address = rs.getString("address");
				String postcode = rs.getString("postcode");
				String postarea = rs.getString("postarea");
				String phone = rs.getString("phone");
				String email = rs.getString("email");
				String date = rs.getString("lastvisit");
				toString = id + " " + fname + " " + lname + " " + address + " "
						+ postcode + " " + postarea + " " + phone + " " + email
						+ " " + date;
				// System.out.println(toString);
				lines.add(toString);
			}
			getSQLConnection().close();
			rs.close();
			stmt.close();

		} catch (SQLException e) {
			System.out.println("SQL:s Catch");
		}
		return lines;
	}

	/**
	 * Updates customers data in database.
	 * 
	 * @param field
	 *            to be updated.
	 * @param value
	 *            new value.
	 * @param id
	 *            primary key value of the customer thats data is to be updated.
	 */

	public void updateCustomer(String field, String value, int id) {

		try {
			stmt = getSQLConnection().createStatement();
			String update = "UPDATE customers SET " + field + " = '" + value
					+ "' WHERE cust_ID=" + id + ";";

			stmt.executeUpdate(update);
			stmt.close();
			getSQLConnection().close();

		} catch (Exception e) {
			System.err.println(e.getClass().getName() + "; " + e.getMessage());
			System.exit(0);
		}
		System.out.println("Customer updated successfully");

	}

	/**
	 * Deletes customer from the database
	 * 
	 * @param id
	 *            primary key value of the customer to be deleted.
	 */

	public void deleteCustomer(int id) {

		try {

			stmt = getSQLConnection().createStatement();
			String delete = "DELETE FROM customers WHERE cust_ID=" + id + ";";
			stmt.execute(delete);
			stmt.close();
			getSQLConnection().close();

		} catch (Exception e) {
			System.err.println(e.getClass().getName() + "; " + e.getMessage());
			System.exit(0);
		}
		System.out.println("Customer deleted successfully");

	}

}
